import getpath
import os
import xlrd
import re
import logging
import Mole
def elementWave(s):
    wave = re.search(r"\d+(\.\d+)?", s).group()
    element = re.search(r"[A-Z][a-z]*", s).group()
    if Mole.Mole.isElement(element) and len(wave)==4 and (element+wave==s or element+"_"+wave==s):
        return(element,wave)
    else:
        return None
def getdata_raw(fname):
    xl_book = xlrd.open_workbook(fname)
    table = xl_book.sheets()[0]
    data = []
    for i in range(table.nrows):
        r = []
        for j in range(table.ncols):
            r.append(table.cell(i, j).value)
        data.append(r)
    return data #None# dataframe1
def zl_data(data):
    data=data[7:]
    r=[]
    record_begin=False
    ele_begin=False
    id1=None
    wt=""
    volume=""
    xishi=""
    for i in range(len(data)):
        row=data[i]
        if row[0]=="Acquire Date:":#new record begin
            if record_begin:
                record={"sampleid":id1,"mass":wt,"volume":volume,"xishi":xishi,"items":eles}#sometime eles not all exists
                r.append(record)
            record_begin=True
            ele_begin=False
            id1=data[i-1][0]
            print("id=",id1)
            continue
        if row[0]=="Color Legend":
            # input("Color")
            if record_begin:
                record_begin=False
                record={"sampleid":id1,"mass":wt,"volume":volume,"xishi":xishi,"items":eles}
                r.append(record)
            break
        if row[0]=="Correction Factor:":
            wt="%.4f" % (float(row[10])/10.0)
            volume=str(float(row[25])*100.0)
            xishi=row[30]
            continue
        if row[0]=="Elem":
            print("record_begin")
            ele_begin=True
            eles={}
            continue
        if ele_begin:
            try:
                elew=elementWave(row[0])
                if elew!=None:
                    (ele,wave)=elew
                    print(ele,wave)
                    # input("pause")
                    wave=wave[:-1]+"."+wave[-1]
                    eles[(ele,wave)]=row[3]
                    print(ele,wave,row[3])
                else:
                    ele_begin=False
            except AttributeError:
                ele_begin=False
            # if row[0]=="":
            #     print("record_end")
            #     record_begin=False
            #     record["items"]=eles
            #     r.append(record)
            #     id1=None
            # else:
            #     eles[row[0]]=row[3]
    return r
def mergeOne(data0,data1):
    # one["items"]
    logging.info(data0)
    logging.info(data1)
    for one in data1["items"].keys():
        if data0["items"].get(one)!=None:
            #equal
            # input("pause")
            return [data0,data1]
        else:
            data0["items"][one]=data1["items"][one]
            # input("else pause")
    logging.info(data0)
    # input("return")
    return [data0]
def merge_data(data):
    print(data)
    r=[]
    first=True
    for i in range(len(data)):
        if first:
            first=False
            if i==len(data)-1:
                r.append(data[i])
            continue
        if data[i]["sampleid"]==data[i-1]["sampleid"]:
            #merge
            merge=mergeOne(data[i-1],data[i])
            if len(merge)==1:
                r.append(merge[0])
                first=True
            else:
                r.append(merge[0])
                r.append(merge[1])
        else:
            r.append(data[i-1])
            if i==len(data)-1:
                r.append(data[i])
    return r    
def bu_one(data0,data1):
    for one in data1["items"].keys():
        if data0["items"].get(one)!=None:
            pass
        else:
            data0["items"][one]=""
    for one in data0["items"].keys():
        if data1["items"].get(one)!=None:
            pass
        else:
            data1["items"][one]=""
    return (data0,data1)        # input("else pause")
def bu_data(data):
    print(data)
    data0=data[0]
    r=[]
    for i in range(len(data)):
        if i==0:
            continue
        (bu,data0)=bu_one(data[i],data0)
        r.append(bu)
    r.insert(0,data0)
    return r    
def dictToList(dict1):
    keys=[]
    vals=[]
    for one in dict1.keys():
        keys.append(one)
        vals.append(dict1[one])
    return (keys,vals)
def tolist(data):
    for one in data:
        (keys,vals)=dictToList(one["items"])
        one["items"]=keys
        one["conc"]=vals
    return data
def getdata(fname):
    data0=getdata_raw(fname)
    data=zl_data(data0)
    data=merge_data(data)
    data=bu_data(data)
    data=tolist(data)
    return data
def test_getdata():
    fname=os.path.join(getpath.getpath(),"..","data","202401121711.xls")#"6300.xls")
    data=getdata(fname)
    return data
if __name__=="__main__":
    data=test_getdata()
    # print(elementWave(""))
    # print(data)
    # for one in data:
    #     print(one["id"])
    # print(data[0])
    # print(data[1])
    # print(data[-1])